﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports MySql.Data.MySqlClient

Public Class TiposProductosDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"
    Public Function existeTipoProducto(ByVal pClave As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM tiposproducto " & _
                                            "WHERE Clave = ?Clave;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Clave", pClave)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function existeTipoProductoPorID(ByVal pId As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM tiposproducto " & _
                                            "WHERE IdTipo = ?Id;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Id", pId)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraTipoProducto(ByVal pTipoProd As TiposProductos) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO tiposproducto(Clave, Descripcion, Estatus, IdFamilia) " & _
                                            "SELECT @Clave, @Descripcion, @Estatus, familias.IdFamilia " & _
                                            "FROM familias " & _
                                            "WHERE familias.IdFamilia =@IdFamilia;"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Clave", pTipoProd.Clave)
                    myComm.Parameters.AddWithValue("@Descripcion", pTipoProd.Descripcion)
                    Select Case pTipoProd.Status
                        Case TiposProductos.EstatusTiposProductos.Activo
                            myComm.Parameters.AddWithValue("@Estatus", "A")
                        Case TiposProductos.EstatusTiposProductos.Inactivo
                            myComm.Parameters.AddWithValue("@Estatus", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Estatus", "A")
                    End Select
                    myComm.Parameters.AddWithValue("@IdFamilia", pTipoProd.Familia.IdFamilia)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaTipoProducto(ByVal pTipoProd As TiposProductos) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " & _
                                            "FROM familias " & _
                                            "WHERE IdFamilia = @IdFamilia;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdFamilia", pTipoProd.Familia.IdFamilia)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) = 0) Then
                        Return False
                    End If

                    myComm.CommandText = "UPDATE tiposproducto " & _
                                            "SET Descripcion = @Descripcion, " & _
                                                "Estatus = @Estatus, " & _
                                                "IdFamilia = @IdFamilia " & _
                                            "WHERE Clave = @Clave; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Descripcion", pTipoProd.Descripcion)
                    Select Case pTipoProd.Status
                        Case TiposProductos.EstatusTiposProductos.Activo
                            myComm.Parameters.AddWithValue("@Estatus", "A")
                        Case TiposProductos.EstatusTiposProductos.Inactivo
                            myComm.Parameters.AddWithValue("@Estatus", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Estatus", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdFamilia", pTipoProd.Familia.IdFamilia)
                    myComm.Parameters.AddWithValue("@Clave", pTipoProd.Clave)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal Clave As String, ByVal setActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE tiposproducto " & _
                                            "SET Estatus = @Estatus " & _
                                            "WHERE Clave = @Clave; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (setActivo) Then
                        myComm.Parameters.AddWithValue("@Estatus", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Estatus", "I")
                    End If
                    myComm.Parameters.AddWithValue("@Clave", Clave)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getTipoProducto(ByVal pClave As String) As TiposProductos
        Dim tmpTipoProd As TiposProductos
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdTipo, Clave, Descripcion, Estatus, " &
                                                "familias.IdFamilia, IdEmpresa, ClaFamilia, DesFamilia, Status  " & _
                                            "FROM tiposproducto " &
                                                    "INNER JOIN familias ON (tiposproducto.IdFamilia = familias.IdFamilia) " & _
                                            "WHERE Clave = @Clave;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Clave", pClave)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpTipoProd = TiposProductosFact.Build(myDR, True)
                        Else
                            tmpTipoProd = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpTipoProd

        Catch ex As Exception
            Throw
        Finally
            tmpTipoProd = Nothing
        End Try
    End Function

    Public Function getListadoTiposProducto(ByVal pClave As String, ByVal pDescripcion As String,
                                            ByVal pClaveFamilia As String, ByVal pEstatus As Enumeraciones.estatusParaBusquedas,
                                            ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                            ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                            ByRef pTotalRegistros As Integer) As List(Of TiposProductos)
        pTotalRegistros = 0
        Dim lsTipoProd As List(Of TiposProductos) = New List(Of TiposProductos)
        Dim strQuery As String = ""
        Dim strQryFamilias As String = ""

        Select Case pEstatus
            Case Enumeraciones.estatusParaBusquedas.Activos
                strQuery = " AND Estatus = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                strQuery = " AND Estatus = 'I' "
            Case Enumeraciones.estatusParaBusquedas.Todos
                strQuery = " "
        End Select

        If (String.IsNullOrWhiteSpace(pClaveFamilia)) Then
            strQryFamilias = ""
        Else
            strQryFamilias = " AND ClaFamilia = @ClaFamilia "
        End If

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdTipo, Clave, Descripcion, Estatus, " &
                                                "familias.IdFamilia, IdEmpresa, ClaFamilia, DesFamilia, Status  " & _
                                            "FROM tiposproducto " &
                                                    "INNER JOIN familias ON (tiposproducto.IdFamilia = familias.IdFamilia) " & _
                                            "WHERE Clave like @Clave " &
                                                    "AND Descripcion LIKE @Descripcion " &
                                                    strQryFamilias &
                                                    strQuery &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.TiposProductos, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Clave", "%" & pClave & "%")
                    myComm.Parameters.AddWithValue("@Descripcion", "%" & pDescripcion & "%")
                    If (strQryFamilias <> "") Then
                        myComm.Parameters.AddWithValue("@ClaFamilia", pClaveFamilia)
                    End If
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While (myDR.Read)
                            Dim tmpTipoProd As TiposProductos = TiposProductosFact.Build(myDR, True)
                            If (tmpTipoProd IsNot Nothing) Then
                                lsTipoProd.Add(tmpTipoProd)
                            End If
                            tmpTipoProd = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return lsTipoProd

        Catch ex As Exception
            Throw
        Finally
            lsTipoProd = Nothing
            strQuery = Nothing
            strQryFamilias = Nothing
        End Try
    End Function
#End Region

    'Public Function registraTipos(ByVal pTipos As TiposProductos) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 4) As OdbcParameter

    '    Try

    '        instrSql = "INSERT INTO ""Tipos""(""ClaFamilia"", ""ClaTipo"", ""DesTipo"", ""IdEmpresa"", ""Status"") " & _
    '                                    "VALUES (?,?,?,?,?);"

    '        oInsParm(0) = New OdbcParameter("", pTipos.ClaFamilia)
    '        oInsParm(1) = New OdbcParameter("", pTipos.Clave)
    '        oInsParm(2) = New OdbcParameter("", pTipos.Descripcion)
    '        oInsParm(3) = New OdbcParameter("", pTipos.Id)
    '        oInsParm(4) = New OdbcParameter("", pTipos.Status)


    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                If (myComm.ExecuteNonQuery() > 0) Then
    '                    Return True
    '                Else
    '                    Return False
    '                End If
    '            End Using
    '        End Using
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function modificaTipos(ByVal pTipos As TiposProductos) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 4) As OdbcParameter
    '    Try
    '        instrSql = "UPDATE ""Tipos"" " & _
    '                                    "SET ""ClaFamilia""=?, ""ClaTipo""=?, ""DesTipo""=?, ""Status""=? " & _
    '                                            "WHERE ""IdEmpresa"" = ?;"

    '        oInsParm(0) = New OdbcParameter("", pTipos.ClaFamilia)
    '        oInsParm(1) = New OdbcParameter("", pTipos.Clave)
    '        oInsParm(2) = New OdbcParameter("", pTipos.Descripcion)
    '        oInsParm(3) = New OdbcParameter("", pTipos.Id)
    '        oInsParm(4) = New OdbcParameter("", pTipos.Status)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                Return True
    '            End Using
    '        End Using

    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function getTipos(ByVal pIdEmpr As Integer, ByVal pStrTipos As String) As List(Of TiposProductos)
    '    Dim TiposLs As List(Of TiposProductos) = New List(Of TiposProductos)
    '    Dim instrSql As String

    '    Try
    '        instrSql = "SELECT ""ClaFamilia"", ""ClaTipo"", ""DesTipo"", ""IdEmpresa"", ""Status"" " & _
    '                                 "FROM ""Tipos"" " & _
    '                                "WHERE ""IdEmpresa"" = ? " & _
    '                                    "AND ""ClaFamilia"" LIKE '%" & pStrTipos & "%' ;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        Dim Tipos As TiposProductos = TiposProductosFact.Build(myDR)
    '                        If (Not Tipos Is Nothing) Then
    '                            TiposLs.Add(Tipos)
    '                        End If
    '                        Tipos = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return TiposLs
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        TiposLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function
End Class
